package com.saoft.dbinfo;

import com.saoft.dbinfo.converts.MySqlTypeConvert;
import com.saoft.dbinfo.po.TableField;
import com.saoft.dbinfo.po.TableInfo;
import com.saoft.dbinfo.querys.MySqlQuery;
import com.saoft.dbinfo.rules.DbType;
import com.saoft.dbinfo.util.NameUtil;
import com.saoft.dbinfo.util.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * Created by saoft on 18/6/4.
 */
public class DbInfoBuilder {

    /**
     * 用途：h2数据库的时候 查找的是所有的表，需要用 schema 条件来过滤
     */
    private String schema;
    /**
     * SQL连接
     */
    private Connection connection;
    /**
     * SQL语句类型
     */
    private IDbQuery dbQuery;
    /**
     * 数据库表信息
     */
    private List<TableInfo> tableInfoList;

    private ITypeConvert typeConvert = new MySqlTypeConvert();

    private boolean toCamel = true;
    private String[] prefix;


    /**
     * 在构造器中处理配置
     * @param conn
     */
    public DbInfoBuilder(Connection conn) {
        handlerDataSource(conn);
    }

    public void setSchema(String schema) {
        this.schema = schema;
    }

    public IDbQuery getDbQuery() {
        return dbQuery;
    }

    public void setDbQuery(IDbQuery dbQuery) {
        this.dbQuery = dbQuery;
    }

    public ITypeConvert getTypeConvert() {
        return typeConvert;
    }

    public void setTypeConvert(ITypeConvert typeConvert) {
        this.typeConvert = typeConvert;
    }

    public boolean isToCamel() {
        return toCamel;
    }

    public void setToCamel(boolean toCamel) {
        this.toCamel = toCamel;
    }

    public String[] getPrefix() {
        return prefix;
    }

    public void setPrefix(String[] prefix) {
        this.prefix = prefix;
    }

    /**
     * <p>
     * 处理数据库表 加载数据库表、列、注释相关数据集
     * </p>
     *
     */
    public void handlerStrategy(String prefix) {
        tableInfoList = getTablesInfo(prefix);
    }

    public void handlerStrategy(String prefix,boolean include) {
        tableInfoList = getTablesInfo(prefix,include);
    }

    /**
     * <p>
     * 处理数据源配置
     * </p>
     *
     */
    private void handlerDataSource(Connection conn) {
        this.connection = conn;
        this.dbQuery = new MySqlQuery();
    }

    private List<TableInfo> getTablesInfo(String prefix) {
        return getTablesInfo(prefix,true);
    }

    /**
     * <p>
     * 获取所有的数据库表信息
     * </p>
     */

    private List<TableInfo> getTablesInfo(String prefix,boolean include) {

        //所有的表信息
        List<TableInfo> tableList = new ArrayList<>();

        PreparedStatement preparedStatement = null;
        try {
            String tablesSql = dbQuery.tablesSql();
            if(include){
                tablesSql += " where `"+dbQuery.tableName()+"` like '"+prefix+"%'";
            }else{
                tablesSql += " where `"+dbQuery.tableName()+"` not like '"+prefix+"%'";
            }

            preparedStatement = connection.prepareStatement(tablesSql);
            ResultSet results = preparedStatement.executeQuery();
            TableInfo tableInfo;
            while (results.next()) {
                /**
                 * h2 的数据库不支持 where tableSchema 只能靠结果过滤
                 */
                if(StringUtils.isNotEmpty(schema)){
                    String string = results.getString(dbQuery.tableSchema());
                    if (StringUtils.isEmpty(string)) {
                        continue;
                    }
                    if (!schema.toUpperCase().equals(string.toUpperCase())) {
                        continue;
                    }
                }
                String tableName = results.getString(dbQuery.tableName());
                if (StringUtils.isNotEmpty(tableName)) {
                    String tableComment = results.getString(dbQuery.tableComment());
                    tableInfo = new TableInfo();
                    tableInfo.setName(tableName);
                    tableInfo.setComment(tableComment);
                    tableList.add(tableInfo);
                } else {
                    System.err.println("当前数据库为空！！！");
                }
            }

            /**
             * 性能优化，只处理需执行表字段 github issues/219
             */
            for (TableInfo ti : tableList) {
                this.convertTableFields(ti);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 释放资源
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return tableList;
    }


    /**
     * <p>
     * 将字段信息与表信息关联
     * </p>
     *
     * @param tableInfo 表信息
     * @return
     */
    private TableInfo convertTableFields(TableInfo tableInfo) {
        boolean haveId = false;
        List<TableField> fieldList = new ArrayList<>();
        try {
            String tableFieldsSql = dbQuery.tableFieldsSql();
            if (DbType.POSTGRE_SQL == dbQuery.dbType()) {
                tableFieldsSql = String.format(tableFieldsSql, "public", tableInfo.getName());
            } else {
                tableFieldsSql = String.format(tableFieldsSql, tableInfo.getName());
            }
            PreparedStatement preparedStatement = connection.prepareStatement(tableFieldsSql);
            ResultSet results = preparedStatement.executeQuery();
            while (results.next()) {
                TableField field = new TableField();
                String key = results.getString(dbQuery.fieldKey());
                // 避免多重主键设置，目前只取第一个找到ID，并放到list中的索引为0的位置
                boolean isId = StringUtils.isNotEmpty(key) && key.toUpperCase().equals("PRI");
                // 处理ID
                if (isId && !haveId) {
                    field.setKeyFlag(true);
                    if (dbQuery.isKeyIdentity(results)) {
                        field.setKeyIdentityFlag(true);
                    }
                    haveId = true;
                } else {
                    field.setKeyFlag(false);
                }
                // 自定义字段查询
                String[] fcs = dbQuery.fieldCustom();
                if (null != fcs) {
                    Map<String, Object> customMap = new HashMap<>();
                    for (String fc : fcs) {
                        customMap.put(fc, results.getObject(fc));
                    }
                    field.setCustomMap(customMap);
                }
                // 处理其它信息
                field.setName(results.getString(dbQuery.fieldName()));
                field.setType(results.getString(dbQuery.fieldType()));
                field.setAbleNull("YES".equalsIgnoreCase(dbQuery.fieldNull()));
                field.setColumnType(typeConvert.processTypeConvert(field.getType()));
                field.setComment(results.getString(dbQuery.fieldComment()));

                field.setPropertyName(processName(field.getName(),prefix));
                fieldList.add(field);
            }
        } catch (SQLException e) {
            System.err.println("SQL Exception：" + e.getMessage());
        }
        tableInfo.setFields(fieldList);
        return tableInfo;
    }

    /**
     * <p>
     * 表信息
     * </p>
     *
     * @return 所有表信息
     */
    public List<TableInfo> getTableInfoList() {
        if (tableInfoList == null) {
            handlerStrategy("");
        }
        return tableInfoList;
    }

    /**
     * <p>
     * 处理表/字段名称
     * </p>
     *
     * @param name
     * @param prefix
     * @return 根据策略返回处理后的名称
     */
    private String processName(String name, String[] prefix) {
        boolean removePrefix = false;
        if (prefix != null && prefix.length >= 1) {
            removePrefix = true;
        }
        String propertyName;
        if (removePrefix) {
            if (toCamel) {
                // 删除前缀、下划线转驼峰
                propertyName = NameUtil.removePrefixAndCamel(name, prefix);
            } else {
                // 删除前缀
                propertyName = NameUtil.removePrefix(name, prefix);
            }
        } else if (toCamel) {
            // 下划线转驼峰
            propertyName = NameUtil.underlineToCamel(name);
        } else {
            // 不处理
            propertyName = name;
        }
        return propertyName;
    }

 
}
